
pip install plotly==5.18.0
pip install dash==0.29.0
pip install dash plotly
pip install --upgrade google-api-python-client google-auth-httplib2 google-auth-oauthlib
pip install gspread
pip install gspread google-auth

  Cell In[1], line 1
    pip install plotly==5.18.0
        ^
SyntaxError: invalid syntax


import os.path
import pandas as pd
import plotly.express as px

from google.auth.transport.requests import Request
from google.oauth2.credentials import Credentials
from google_auth_oauthlib.flow import InstalledAppFlow
from googleapiclient.discovery import build
from googleapiclient.errors import HttpError

# If modifying these scopes, delete the file token.json.
SCOPES = ["https://www.googleapis.com/auth/spreadsheets.readonly"]

def create_dataframe(spreadsheet_id, range_name):
    """Create a DataFrame from a Google Spreadsheet."""
    creds = None
    if os.path.exists("token.json"):
        creds = Credentials.from_authorized_user_file("token.json", SCOPES)
    if not creds or not creds.valid:
        if creds and creds.expired and creds.refresh_token:
            creds.refresh(Request())
        else:
            flow = InstalledAppFlow.from_client_secrets_file(
                "credentials.json", SCOPES
            )
            creds = flow.run_local_server(port=0)
        with open("token.json", "w") as token:
            token.write(creds.to_json())

    try:
        service = build("sheets", "v4", credentials=creds)

        # Call the Sheets API
        sheet = service.spreadsheets()
        result = sheet.values().get(spreadsheetId=spreadsheet_id, range=range_name).execute()
        values = result.get("values", [])

        if not values:
            print("No data found.")
            return pd.DataFrame()  # Return an empty DataFrame if no data is found

        # Convert the values to a pandas DataFrame, skipping the first row
        df = pd.DataFrame(values[1:], columns=values[0])

        return df
    except HttpError as err:
        print(err)
        return pd.DataFrame()  # Return an empty DataFrame in case of an error


# Access spreadsheet
SPREADSHEET_ID = "1e1EvgnSxAce8OcCqXCvBsnUQVlCHPIf5wA6e-UvA59c"
RANGE_NAME = "'Net Sales By Market'!A1:C61"

# Call the function to create a new DataFrame
market = create_dataframe(SPREADSHEET_ID, RANGE_NAME)


market = market.replace({',': '', '\.00': ''}, regex=True)
market["Sales"] = market["Sales"].astype(int)
#market.set_index("Year", inplace=True)


# Reverse year to get ascending order
r_market= market.iloc[::-1]
r_market


# Create a line graph for sales for each region across years
line_chart = px.line(r_market, x="Year", y="Sales", color="Region", markers=True, title="Sales For Each Region Across Years", height=600, template="plotly_dark")

line_chart.update_traces(mode="markers+lines", hovertemplate=None)
line_chart.update_layout(hovermode="x unified", title_x=0.5)
line_chart.show()


#fig.update_layout(hovermode="closest")
#fig.layout.update(showlegend=False) 
#fig.update_layout(title_text='Your title', title_x=0.5)


# Create a bar chart with custom color and animation
bar_chart = px.bar(
    r_market, 
    x="Region", 
    y="Sales", 
    animation_frame="Year", 
    animation_group="Region", 
    hover_name="Region", 
    range_y=[0, 180000], 
    height=600, 
    template="plotly_dark",
    text_auto='.2s',
    title="Sales By Region"
)

bar_chart.update_yaxes(
    autorange=False,
    tick0=0,
    dtick=50000,
    visible=False
)

# Set custom color for the region
custom_color = '#4ba65f'

# Loop through frames and update traces with custom color
for i, frame in enumerate(bar_chart.frames):
    frame_data = frame.data[0]
    frame_data.update(marker_color=custom_color, marker_line_color='white',textposition="outside")

bar_chart.update_traces(marker_color = '#4ba65f', marker_line_color = 'white', textposition="outside")
bar_chart.update_layout(hovermode=False,title_x=0.5, xaxis_title="")

bar_chart.show()


import dash
from dash import dcc
from dash import html
from dash.dependencies import Input, Output

# Dash app
app = dash.Dash(__name__)

# Layout of the dashboard
app.layout = html.Div([
    # Second chart (Line chart)
    #dcc.Graph(id='line-chart', figure=line_chart),
    
    # First chart (Bar chart)
    dcc.Graph(id='bar-chart', figure=bar_chart)


])

if __name__ == '__main__':
    app.run_server(port=8051, debug=True)

	Year	Region	Sales
59	2011	Rest of Asia Pacific	9902
58	2012	Rest of Asia Pacific	10741
57	2013	Rest of Asia Pacific	11181
56	2014	Rest of Asia Pacific	11248
55	2015	Rest of Asia Pacific	15093
54	2016	Rest of Asia Pacific	13654
53	2017	Rest of Asia Pacific	15199
52	2018	Rest of Asia Pacific	17407
51	2019	Rest of Asia Pacific	17788
50	2020	Rest of Asia Pacific	19593
49	2021	Rest of Asia Pacific	26356
48	2022	Rest of Asia Pacific	29375
47	2011	Japan	5437
46	2012	Japan	10571
45	2013	Japan	13462
44	2014	Japan	15314
43	2015	Japan	15706
42	2016	Japan	16928
41	2017	Japan	17733
40	2018	Japan	21733
39	2019	Japan	21506
38	2020	Japan	21418
37	2021	Japan	28482
36	2022	Japan	25977
35	2011	Greater China	12690
34	2012	Greater China	22533
33	2013	Greater China	25417
32	2014	Greater China	31853
31	2015	Greater China	58715
30	2016	Greater China	48492
29	2017	Greater China	44764
28	2018	Greater China	51942
27	2019	Greater China	43678
26	2020	Greater China	40308
25	2021	Greater China	68366
24	2022	Greater China	74200
23	2011	Europe	27778
22	2012	Europe	36323
21	2013	Europe	37883
20	2014	Europe	44285
19	2015	Europe	50337
18	2016	Europe	49952
17	2017	Europe	54938
16	2018	Europe	62420
15	2019	Europe	60288
14	2020	Europe	68640
13	2021	Europe	89307
12	2022	Europe	95118
11	2011	Americas	38315
10	2012	Americas	57512
9	2013	Americas	62739
8	2014	Americas	80095
7	2015	Americas	93864
6	2016	Americas	86613
5	2017	Americas	96600
4	2018	Americas	112093
3	2019	Americas	116914
2	2020	Americas	124556
1	2021	Americas	153306
0	2022	Americas	169658